﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Net.Mime;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Common.Data;
using Common.Helpers;
using Converter.Irony.Grammars;
using Converter.SqlXml;
using Ionic.Zip;
using Irony.Parsing;
using Models.Entities;
using Oracle.DataAccess.Client;

namespace XMLSQL
{
    public partial class Query : System.Web.UI.Page
    {
        protected IAdminDataProvider _dataProvider;
        protected System.Security.Principal.IPrincipal _user;

        protected void Page_Load(object sender, EventArgs e)
        {
            _dataProvider = new AdminDataProvider();
            _user = HttpContext.Current.User;

            //load up the drop down list with connection strings
            var connectionStrings = _dataProvider.GetConnectionStringsByUser(_user.Identity.Name);

            if (!IsPostBack)
            {
                DropDownList1.DataTextField = GetPropertyName(() => new ConnectionString().connStrName);
                DropDownList1.DataValueField = GetPropertyName(() => new ConnectionString().keyName);
                DropDownList1.DataSource = connectionStrings;
                DropDownList1.DataBind();
            }
        }

        /// <summary>
        /// Gets the property name of a class.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="propertyExpression"></param>
        /// <returns></returns>
        public string GetPropertyName<T>(Expression<Func<T>> propertyExpression)
        {
            return (propertyExpression.Body as MemberExpression).Member.Name;
        }

        protected void Button3_Click(object sender, EventArgs e)
        {
            //get the connection string the user selected from the drop down list
            string connectionString = _dataProvider.GetStringForConnection(DropDownList1.SelectedValue);

            var isOracle = DropDownList1.SelectedValue.ToUpper().Contains("ORACLE");

            //clear the textboxes
            xmlTextBox.Text = string.Empty;
            schemaTextBox.Text = string.Empty;

            var grammar = new XmlSqlGrammar();
            var language = new LanguageData(grammar);
            var parser = new Parser(language);
            string result = string.Empty;

            var query = TextBox1.Text.Trim();

            //check if the query contains anything other than "select" as the initial token
            if (!string.IsNullOrEmpty(query) && !query.Split(' ')[0].ToUpper().Equals("SELECT"))
            {
                xmlTextBox.Text = "Only SELECT commands are allowed.";
                schemaTextBox.Text = string.Empty;
            }
            else //it's a select command, so proceed
            {
                //parse the query
                var parseTree = ParseSql(parser, query);

                //if parsing succeeded
                if (parseTree.Root != null)
                {
                    //establish the converter
                    ISqlXmlConverter converter = new SqlXmlConverter(parseTree);

                    //build the pattern
                    converter.BuildSqlXmlPattern(parseTree);

                    //clean the sql query
                    var sql = XmlConversionHelper.CleanSql(query);

                    if (isOracle)
                    {
                        //execute the query
                        using (var connection = new OracleConnection(connectionString))
                        {
                            try
                            {
                                connection.Open();

                                var command = new OracleCommand(sql, connection);

                                //get the metadata from the database when the reader is executed
                                var reader = command.ExecuteReader(CommandBehavior.KeyInfo);

                                //get the column information
                                var schemaTable = reader.GetSchemaTable();
                                var columnData = new List<Dictionary<string, string>>();

                                //count the number of fields in the reader
                                int fieldCount = 0;

                                //gets the info for each column; ignores junk schema data from the reader
                                foreach (DataRow row in schemaTable.Rows)
                                {
                                    //only get visible fields
                                    if (fieldCount <= reader.VisibleFieldCount)
                                    {
                                        var columnInfo = new Dictionary<string, string>();
                                        columnInfo.Add("TableName", row["BaseTableName"].ToString());
                                        columnInfo.Add("ColumnName", row["columnName"].ToString());
                                        columnData.Add(columnInfo);
                                    }

                                    fieldCount++;
                                }

                                if (!reader.HasRows)
                                {
                                    result = "No rows found.";
                                }
                                else
                                {
                                    //loop through the reader
                                    while (reader.Read())
                                    {
                                        var rowData = new List<SqlXmlColumn>();

                                        for (int x = 0; x < reader.VisibleFieldCount; x++)
                                        {
                                            var value = reader.IsDBNull(x) ? string.Empty : reader.GetValue(x).ToString();
                                            rowData.Add(new SqlXmlColumn
                                            {
                                                value = value,
                                                columnName = reader.GetName(x),
                                                columnInfo = columnData[x]
                                            });
                                        }

                                        converter.SetSqlXmlNodeMetadata(rowData);
                                    }
                                }

                                reader.Close();
                                connection.Close();
                            }
                            catch (Exception ex)
                            {
                                result = ex.Message;
                            }
                        }
                    }
                    else
                    {
                        using (var connection = new OleDbConnection(connectionString))
                        {
                            try
                            {
                                connection.Open();

                                var command = new OleDbCommand(sql, connection);

                                //get the metadata from the database when the reader is executed
                                var reader = command.ExecuteReader(CommandBehavior.KeyInfo);

                                //get the column information
                                var schemaTable = reader.GetSchemaTable();
                                var columnData = new List<Dictionary<string, string>>();

                                //count the number of fields in the reader
                                int fieldCount = 0;

                                //gets the info for each column; ignores junk schema data from the reader
                                foreach (DataRow row in schemaTable.Rows)
                                {
                                    //only get visible fields
                                    if (fieldCount <= reader.VisibleFieldCount)
                                    {
                                        var columnInfo = new Dictionary<string, string>();
                                        columnInfo.Add("TableName", row["BaseTableName"].ToString());
                                        columnInfo.Add("ColumnName", row["columnName"].ToString());
                                        columnData.Add(columnInfo);
                                    }

                                    fieldCount++;
                                }

                                if (!reader.HasRows)
                                {
                                    result = "No rows found.";
                                }
                                else
                                {
                                    //loop through the reader
                                    while (reader.Read())
                                    {
                                        var rowData = new List<SqlXmlColumn>();

                                        for (int x = 0; x < reader.VisibleFieldCount; x++)
                                        {
                                            var value = reader.IsDBNull(x) ? string.Empty : reader.GetValue(x).ToString();
                                            rowData.Add(new SqlXmlColumn
                                            {
                                                value = value,
                                                columnName = reader.GetName(x),
                                                columnInfo = columnData[x]
                                            });
                                        }

                                        converter.SetSqlXmlNodeMetadata(rowData);
                                    }
                                }

                                reader.Close();
                                connection.Close();
                            }
                            catch (Exception ex)
                            {
                                result = ex.Message;
                            }
                        }
                    }
                    //if no errors
                    if (string.IsNullOrEmpty(result))
                    {
                        //get the xml
                        var xml = converter.GetXml();
                        var xsd = XmlConversionHelper.GetXsdForXml(xml);

                        xmlTextBox.Text = xml;
                        schemaTextBox.Text = xsd;
                    }
                    else //an error occurred
                    {
                        xmlTextBox.Text = result;
                        schemaTextBox.Text = string.Empty;
                    }

                    //xmlTextBox.Text = xml;
                    //schemaTextBox.Text = xsd;
                }
                else
                {
                    //do something here
                    xmlTextBox.Text = string.IsNullOrEmpty(result) ? "Parsing failed!" : result;
                    schemaTextBox.Text = string.Empty;
                }
            }
        }

        private ParseTree ParseSql(Parser parser, string sql)
        {
            if (!parser.Language.CanParse())
                return null;

            parser.Parse(sql, "<source>");

            return parser.Context.CurrentParseTree;
        }

        //download button
        protected void Button5_Click(object sender, EventArgs e)
        {
            //grab the info from the xml and xsd text boxes
            if (!string.IsNullOrEmpty(xmlTextBox.Text) && !string.IsNullOrEmpty(schemaTextBox.Text))
            {
                var xml = xmlTextBox.Text;
                var xsd = schemaTextBox.Text;

                using (var zip = new ZipFile())
                {
                    //add the entries
                    zip.AddEntry("query.xml", Encoding.UTF8.GetBytes(xml));
                    zip.AddEntry("query.xsd", Encoding.UTF8.GetBytes(xsd));

                    //get the filename
                    var fileName = "query.zip";

                    //set the content disposition for the response
                    var cd = new ContentDisposition
                    {
                        FileName = fileName,
                        Inline = false,
                    };

                    HttpContext.Current.Response.Clear();
                    HttpContext.Current.Response.ContentType = "application/zip";
                    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=query.zip");
                    zip.Save(HttpContext.Current.Response.OutputStream);
                    HttpContext.Current.Response.End();
                }
            }
        }

        protected void Button4_Click(object sender, EventArgs e)
        {
            //clear the text boxes
            TextBox1.Text = string.Empty;
            xmlTextBox.Text = string.Empty;
            schemaTextBox.Text = string.Empty;
        }
    }
}